package com.ifnk.portaljava.hlyt.dao.impl;

import com.ifnk.portaljava.dapWeb.advance.DBAccess;
import com.ifnk.portaljava.dapWeb.pojo.CDRrecord;
import com.ifnk.portaljava.hlyt.beans.Behaviour;
import com.ifnk.portaljava.hlyt.dao.IBehaviourJdbcDAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BehaviourJdbcDaoImpl implements IBehaviourJdbcDAO {

	public int save(int userId, String userName, Connection conn)
	throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			stmt = conn.createStatement();
			String sql = "insert into behaviour(id,name) values(" + userId
			+ ",'" + userName + "')";
//			System.out.println("insert sql:" + sql);
			stmt.execute(sql);
		} catch (SQLException e) {
			throw new Exception("save behaviour error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;
	}

	@SuppressWarnings("finally")
	public List<CDRrecord> findAllCDRFields(Boolean haveProtocols) throws Exception {
		List<CDRrecord> cdrFieldList = new ArrayList<CDRrecord>();
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		String sql = "select id, p_displayname, p_name, width, p_protocol, selected  from cdrfield  where (p_displayname is not null) and (p_protocol is not null) order by id";
		rs = dbQuery.queryTable(sql);
		List<String> protocolsList = new ArrayList<String>();
		Map<String,Integer> cdrFieldNameMap = new HashMap<String,Integer>();
		int cdrFieldListIndex = 0;
		try {
			while (rs.next()) {
				CDRrecord cdr = new CDRrecord();
				try {
					String p_name = rs.getString("p_name");
					//int cdr_id = rs.getInt("id");
					if(cdrFieldNameMap.get(p_name)==null){
					cdrFieldNameMap.put(p_name, cdrFieldListIndex);
					cdrFieldListIndex++;
					}else{
						int index = (Integer) cdrFieldNameMap.get(p_name);
						cdr = (CDRrecord) cdrFieldList.get(index);
						String sqlProtocol = "";
						try{
							sqlProtocol = rs.getString("p_protocol");
						}catch(Exception e){
							e.printStackTrace();
						}
						String newProtocol = cdr.getProtocol()+","+sqlProtocol;
						newProtocol = newProtocol.replaceAll(",,", ",");
//						newProtocol = newProtocol.substring(0, newProtocol.length()-1);
						cdr.setProtocol(newProtocol);
						continue;
					}
				} catch (RuntimeException e) {
					e.printStackTrace();
					continue;
				}
				try {
					cdr.setDisplayname(rs.getString("p_displayname"));
				} catch (RuntimeException e) {
					cdr.setDisplayname("");
					e.printStackTrace();
				}

				try {
					cdr.setName(rs.getString("p_name"));
				} catch (RuntimeException e) {
					cdr.setName("");
					e.printStackTrace();
				}

				try {
					cdr.setWidth(rs.getInt("width"));
				} catch (RuntimeException e) {
					cdr.setWidth(0);
					e.printStackTrace();
				}

				try {
					String sqlProtocol = rs.getString("p_protocol");
					cdr.setProtocol(sqlProtocol);
					if(!protocolsList.contains(sqlProtocol)){
						protocolsList.add(sqlProtocol);
					}
				} catch (RuntimeException e) {
					cdr.setProtocol("");
					e.printStackTrace();
				}

				try {
					cdr.setSelected(rs.getString("selected")+"");
				} catch (RuntimeException e) {
					cdr.setSelected("");
					e.printStackTrace();
				}
				cdrFieldList.add(cdr);

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("find All ColumnHeaders error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
//			if (haveProtocols) {
//				cdrFieldList.add(protocolsList);
//			}
//			System.out.println("list.size()="+cdrFieldList.size());
			return cdrFieldList;
		}
	}
	
	public List findAllCDRFieldsName() {
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		String sqlStr = "select distinct p_name from cdrfield";
		rs = dbQuery.queryTable(sqlStr);
		List list = new ArrayList();
		try {
			while(rs.next()) {
				list.add(rs.getString("p_name"));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	public List findCDRFieldByUserSelectedField(String field) throws Exception {
		String[] fields = null;
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		if(field != null) {
			fields = field.split(",");
			field = "";
			for(int i=0; i < fields.length; i++) {
				field += fields[i].split(":")[0] + ",";
				map.put(fields[i].split(":")[0].replace("'", ""), Integer.parseInt(fields[i].split(":")[1]));
			}
			if(field != "") {
				field = field.substring(0, field.length() - 1);
			}
		}
		List list = new ArrayList();
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		String sql = "select id, p_displayname, p_name, width, p_protocol, selected  from cdrfield  where (p_displayname is not null) and (p_protocol is not null) and (selected ='1') group by p_name order by id";
		if (field != null) {
			sql = "select id, p_displayname, p_name, width, p_protocol, selected  from cdrfield  where (p_displayname is not null) and (p_protocol is not null) and p_name in ("
				+ field + ") group by p_name";
		}
		rs = dbQuery.queryTable(sql);

		try {
			int i = 0;
			while (rs.next()) {
				CDRrecord cdrrecord = new CDRrecord();
				try {
					cdrrecord.setId(rs.getInt("id"));
				} catch (RuntimeException e) {
					cdrrecord.setId(0);
					e.printStackTrace();
				}

				try {
					cdrrecord.setDisplayname(rs.getString("p_displayname"));
				} catch (RuntimeException e) {
					cdrrecord.setDisplayname("");
					e.printStackTrace();
				}

				try {
					cdrrecord.setName(rs.getString("p_name"));
				} catch (RuntimeException e) {
					cdrrecord.setName("");
					e.printStackTrace();
				}

				try {
					if(fields == null) {
						cdrrecord.setWidth(rs.getInt("width"));
					} else {
						cdrrecord.setWidth(map.get(cdrrecord.getName()));
					}
				} catch (RuntimeException e) {
					cdrrecord.setWidth(0);
					e.printStackTrace();
				}

				try {
					cdrrecord.setProtocol(rs.getString("p_protocol"));
				} catch (RuntimeException e) {
					cdrrecord.setProtocol("");
					e.printStackTrace();
				}

				try {
					cdrrecord.setSelected(rs
							.getString("selected")+"");
				} catch (RuntimeException e) {
					cdrrecord.setSelected("");
					e.printStackTrace();
				}
				list.add(cdrrecord);
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("find CDRFields with user error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	public Behaviour findUserFieldByUserId(int id) throws Exception {
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		String sql = "select id, name, selectedfield,lastmodule from behaviour where id="
			+ id;
		rs = dbQuery.queryTable(sql);
		Behaviour obj = null;
		try {
			while (rs.next()) {
				obj = new Behaviour();
				try {
					obj.setId(rs.getInt("id"));
				} catch (RuntimeException e) {
					obj.setId(0);
					e.printStackTrace();
				}
				try {
					obj.setName(rs.getString("name"));
				} catch (RuntimeException e) {
					obj.setName("");
					e.printStackTrace();
				}
				try {
					obj.setSelectedField(rs.getString("selectedfield"));
				} catch (RuntimeException e) {
					obj.setSelectedField("");
					e.printStackTrace();
				}
				try {
					obj.setLastModule(rs.getString("lastmodule"));
				} catch (RuntimeException e) {
					obj.setSelectedField("");
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("find User Field By UserId error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return obj;
	}

	// 添加或者更新用户的字段
	public void updateUserFields(String selectedField, int userId,
			Connection conn) throws Exception {
		PreparedStatement pstm = null;
		String sql = "update behaviour set selectedfield=? where id=?";
//		System.out.println("********************"+sql+"********************");
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, selectedField);
			pstm.setInt(2, userId);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("update UserField error!");
		} finally {
			try {
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public void updateLastModule(int id, String url, Connection conn)
	throws Exception {

		PreparedStatement pstm = null;
		try {
			String sql = "update behaviour set lastmodule=? where id=?";
//			System.out.println("sql:"+sql);
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, url);
			pstm.setInt(2, id);
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("update lastmodule error!");
		} finally {
			try {
				if (pstm != null) {
					pstm.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void deleteByUserId(int userId, Connection conn) throws Exception {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "delete from behaviour where id=" + userId;
//			System.out.println("sql:" + sql);
			stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception ("delete behaviour error!");
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}
	
	public static void main(String arg[]){
		BehaviourJdbcDaoImpl bi = new BehaviourJdbcDaoImpl();
		try {
			bi.findAllCDRFields(true);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
